Perform Common Management Tasks

Introduction

During this lab, you will learn how to perform some common management tasks and items.

Objectives

At the end of this lab, you will be able to:

  • Suspend and Resume synchronization
  • Add/Remove a database and replica
  • Manually failover an Availability Group

Estimated Time

45 minutes

Logon Information

Before Login make sure windows has Applied Computer Setting to all nodes.

Use the following credentials to login into virtual environment

  1. Connect to AlwaysOnClient as Corpnet\Cluadmin using Pa$$w0rd as the password.

    Click the Type Text icon to enter the associated text into the virtual machine.

  2. Connect to AlwaysOnN1 as CORPNET\Administrator using Pa$$w0rd as the password.

    Click the Type Text icon to enter the associated text into the virtual machine.

  3. Connect to AlwaysOnN2 as CORPNET\Administrator using Pa$$w0rd as the password.

    Click the Type Text icon to enter the associated text into the virtual machine.

  4. Connect to AlwaysOnN3 as CORPNET\Administrator using Pa$$w0rd as the password.

    Click the Type Text icon to enter the associated text into the virtual machine.

  5. Connect to AlwaysOnDC as Administrator using Pa$$w0rd as the password.

    Click the Type Text icon to enter the associated text into the virtual machine.

  6. Change the screen resolution if required.

    You may want to adjust the screen resolution to your own preference. Do this by right-clicking on the desktop and choosing Screen resolution and clicking OK when finished.

Exercise 1: Suspend and resume data movement then change synchronization

This exercise shows how to suspend and resume the data movement on a per database level and how to change the synchronization type for a replica.

Tasks

  1. Login to the AlwaysOnClient virtual machine as Corpnet\CluAdmin using Pa$$w0rd as the password.

    Click the Type Text icon to enter the associated text into the virtual machine.

  2. Open SQL Server Management Studio (SSMS)

    1. Open SSMS by left clicking the icon on the taskbar.

  3. Connect to the listener

    1. In the Connect to Server dialog box, type in AGCorpListen and then click Connect.

  4. Suspend data movement of the AdventureWorks database

    1. Expand the AlwaysOn High Availability Folder in SSMS

    2. Expand the Availability Groups Folder

    3. Expand the AGCorp availability group

    4. Expand the Availability Databases folder

    5. Right-Click on the AdventureWorks database and choose Suspend Data Movement

    6. This will bring up a Suspend Data Movement dialog box, press OK

    7. Do you notice any changes in the icon for the database?

  5. View the status of synchronization for the AdventureWorks database

    1. Right-Click on the AGCorp availability group in SSMS and choose Show Dashboard

    2. What does the dashboard show? How could we use this to diagnose an issue?

    3. Open the 08_Synchronization_Status script from the AlwaysOnClient computer in folder c:\scripts and execute it. What DMVs are being used? Is there a reason why synchronization has stopped?

  6. Resume data movement

    1. Open a new query window while connected to AGCorpListen

    2. Make sure the database context is set to master and type in: 

      TSQL
      ALTER DATABASE AdventureWorks SET HADR RESUME

    3. Execute the T-SQL statement

    4. Right-Click the AGCorp availability group and choose Refresh

    5. Expand the Availability Databases Folder under the AGCorp availability group

    6. What does the icon look like now?

    7. Go to the AlwaysOn dashboard that was opened in step 4. Press F5 to manually refresh the dashboard. What does the dashboard show, now?

    8. Run the 08_Synchronization_Status script again, what do the results say?

    9. Why were all the replicas affected when we suspended data movement?

  7. Change the synchronization of the replica

    1. Right-click on the AGCorp availability group and choose Properties to bring up the Availability Group Properties dialog box.

    2. In the bottom pane under Availability Replicas, choose a secondary replica and click on the dropdown under Availability Mode, change it to asynchronous commit.

    3. Click OK on the dialog. Does an error occur? If so, why?

    4. Click OK to close the error dialog.

    5. Change the failover mode to Manual, click OK.

    6. If the Availability Group Dashboard is not already open, right-click on the AGCorp availability group and open the dashboard.

    7. Did the synchronization state change for the now asynchronous replica? Why did it change?

Congratulations!

You have successfully completed this exercise. Click Next to advance to the next exercise.

Exercise 2: Add and remove a database and replica

This exercise shows how to add and remove a database from an availability group.

Task 1: Remove the AdventureWorks database from the availability group from the primary node

  1. Login to the AlwaysOnClient virtual machine as Corpnet\CluAdmin using Pa$$w0rd as the password.

    Click the Type Text icon to enter the associated text into the virtual machine.

  2. Open SQL Server Management Studio (SSMS)

    1. Open SSMS by left clicking the icon on the taskbar.

  3. Connect to the listener

    1. In the Connect to Server dialog box, type in AGCorpListen and then click Connect.

  4. Expand the AlwaysOn High Availability folder in SSMS

  5. Expand the Availability Groups folder

  6. Expand the AGCorp availability group

  7. Expand the databases folder

  8. Right-click on the AdventureWorks database and choose Remove Database from Availability Group…

  9. The Remove Database from Availability Group dialog is now shown, press OK.

  10. Right-Click on the AGCorp availability group and choose show dashboard if it isn't already showing.

  11. Does the AdventureWorks database exist on any other replicas?

  12. Connect directly to a secondary replica using object explorer by clicking Connect in object explorer and choosing Database Engine…

  13. Type in a secondary replica name and choose to connect.

  14. Expand the databases folder in SSMS. Does the AdventureWorks database exist on the secondary replica? If so, what state is it in?

Task 2: Add the AdventureWorks database into the availability group

  1. Connect to the listener, if not already connected.

  2. In object explorer expand AGCorpListen, then AlwaysOn High Availability folder down until you’ve expanded the Availability Databases folders if not already done.

  3. Right-click on the Availability Databases folder for the AGCorp availability group and choose Add Database…

  4. This will bring up the Add Database to Availability Group wizard

  5. If you start at the introductory page, click next.

  6. In the "Select Databases" page, you'll notice the AdventureWorks database already meets the prerequisites. Check the box next to the name and click next.

  7. In the Connect o Replicas page, in the lower right-hand corner, click Connect All…, then click Connect on the connection dialog. Click Next.

  8. In the Select Data Synchronization page, choose the Join Only option and click Next

  9. You'll be presented with the validation page with all of the validation results as skipped. Click Next.

  10. Finally, you'll arrive at the summary page. Click Finish.

  11. When the wizard finishes, click Close.

  12. Is the AdventureWorks database back in the availability group? What are some ways we can tell?

Taks 3: Remove a replica from an availability group

  1. If not already expanded, expand the Availability Replicas folder under the AGCorp availability group.

  2. Right-click on a secondary replica and choose Remove from Availability Group. This will bring up the Remove Secondary Replica from Availability Group dialog.

  3. Click OK.

  4. Connect directly to the SQL instance of the recently removed replica.

  5. Expand the Databases folder in SSMS for that replica. What state are the databases in?

Task 4: Add a replica into the availability group

  1. If not connected, connect to the primary replica by using the listener name in SSMS.

  2. Expand the folder under AlwaysOn High Availability down to the Availability Replicas for the AGCorp availability group.

  3. Right-click on the Availability Replicas folder and choose Add Replica… This will bring up the Add Replica wizard.

  4. Click Next.

  5. In the lower right-hand corner, click Connect All… and then connect with the default credentials. Click Next.

  6. Specify the replica previously removed by clicking Add Replica…

  7. Enter the name of the SQL instance from which you previously removed the replica, and click Connect.

  8. The replica should now show in the Replicas list. Do NOT check the Automatic Failover checkbox and change Readable Secondary to YES. Click Next.

    ptemo83x.png

  9. Choose the Join Only option and click Next.

  10. On the validation page, click Next.

  11. On the Summary page, click Finish.

  12. Why could we add the replica back in using Join Only?

Congratulations!

You have successfully completed this exercise. Click Next to advance to the next exercise.

Exercise 3: Manually fail over an availability group

This exercise shows how to manually fail over an availability group using the wizard and T-SQL.

Task 1: Manually failover the AGCorp availability group using the wizard

  1. In object explorer, choose Connect then Database Engine…

  2. Supply the listener name, if not already connected. This will connect us to the primary instance of the availability group.

  3. Expand the folder under AlwaysOn High Availability until you reach the AGCorp availability group.

  4. Right-Click on the AGCorp availability group and choose Failover… This will bring up the Fail Over Availability Group wizard.

  5. Click Next on the introductory page.

  6. When presented with the options of which replicas we'd like to become the primary, do you notice any differences between the two possible choices?

    Which one would be the optimal choice?

  7. Click on the Synchronous Commit replica, the one with the green checkbox next to it and press Next.

  8. We need to connect to the replica that will become the primary instance. Click the Connect… button and use the defaulted credentials. Click Connect. Click Next on the wizard.

  9. The summary page now shows us the action we are going to take, failing over from one replica to another. Click Finish.

  10. Open a new query window, connecting to the listener AGCorpListen. Run the following T-SQL Query: 

    TSQL
    SELECT @@ServerName

  11. What replica name comes back? Was this expected?

Congratulations!

You have successfully completed this exercise. You can move to the next lab.